package cn.com.enboga.musicbox.db
{
	import flash.data.SQLConnection;
	import flash.data.SQLMode;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.filesystem.File;
	
	public class LocalDbConnection
	{
		//定义一个用来连接数据库的 SQLConnection
		private static var conn:SQLConnection;
		
		//private var isCreateConnection = false;
		
		//定义数据库文件
		private static var dbFile:File = File.applicationStorageDirectory.resolvePath("musicBoxDB.db");
		
		public function LocalDbConnection()
		{
			
		}
		
		public static function getConnection(sqlMode:String):SQLConnection{
			var _conn:SQLConnection = new SQLConnection();
			try{
				if(sqlMode != null && sqlMode != ""){
					_conn.open(dbFile, sqlMode);
				}else{
					_conn.open(dbFile);
				}
			}catch(e:Error){
				trace("连接数据库时出错: " + e.message);
				_conn.open(dbFile, SQLMode.UPDATE);
			}

			return _conn;
		}
				
		public static function sqlExecute(sqlStr:String, sqlMode:String, isResult:Boolean):SQLResult
		{
			try{
				conn = getConnection(sqlMode);
				var sqlStatement:SQLStatement = new SQLStatement();
				sqlStatement.sqlConnection = conn;
				sqlStatement.text = sqlStr;
				
				//执行SQL语句
				sqlStatement.execute();
				
				if(isResult){
					return sqlStatement.getResult();
				}else{
					return null;
				}
				
				conn.close();
				conn = null;
			}catch(e:Error){
				trace("执行SQL语句时出错: " + e.message);
			}
			
			return null;
		}
		
		public function createMusicBoxInfo():void
		{	
			var sql:String = 
					"CREATE TABLE IF NOT EXISTS musicboxinfo (" + 
					" PID INTEGER PRIMARY KEY AUTOINCREMENT, " + 
					" pname TEXT, " + 
					" pvalue TEXT )";
					
			sqlExecute(sql, SQLMode.CREATE, false);
		}
		
		public function insertMusicBoxInfo():void{			
			var sql:String = 
					"INSERT INTO musicboxinfo(pname,pvalue) VALUES" + 
					" ('默认列表', '1.m3u') ";
					
			sqlExecute(sql, SQLMode.UPDATE, false);
		}

		public function selectMusicBoxInfo():String{
			var sql:String = 
					"SELECT * FROM musicboxinfo";
					
			var result:SQLResult = sqlExecute(sql, SQLMode.UPDATE, true);
			
			var numResults:int = result.data.length;
			
			for(var i:int=0; i<numResults; i++){
				var row:Object = result.data[i];
				
				trace(row.pname + ":" + row.pvalue);
				sql = row.pvalue;
			}

			return sql;
		}
		
	}
}